package com.supermap.dataservice.user.dao;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.user.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author lxs
 * @Title:服务对象类型相关 数据层
 * @Description:
 * @date 2018/7/26  16:46
 */
@Mapper
public interface UserTypeDao {

    @Select("select * from t_role ")
    List<Role> getRoleList();

    @Select("select t1.id,t1.username,t3.code,t4.Lon as longitude ,t4.Lat latitude from t_user t1 INNER JOIN t_user_role t2 "+
            "on t1.id=t2.userid INNER JOIN t_role t3 on t3.id=t2.roleid INNER JOIN t_vip t4 on t1.id=t4.userId "+
            "where t1.areaCode like #{areaCode} and IF(ISNULL(#{code}),true,t3.code=#{code}) ")
    List<UserType> getUserTypeList(@Param("areaCode") String areaCode, @Param("code") String code);

    @Select("select *,v.Lat as latitude, v.Lon as longitude from t_vip v where userid=#{id} ")
    List<Vip> getVipById(@Param("id") String id);
    @Select("select id,username,mobile,address,Email,Introduction,areaCode from t_user where id=#{id} ")
    User getUserById(@Param("id") String id);

    @Select("select * from t_vip where userId=#{userId}")
    Vip getVipByUserId(@Param("userId") String userId);

    @Update("update t_vip set epath=#{epath} where userId=#{userId}")
    int updateEpathByUserId(@Param("epath") String epath,@Param("userId") String userId);

    @Update("update t_user_role set roleid=4 where userid=#{userId}")
    int updateUserRole(@Param("userId") String userId);

    @Insert("insert into t_user_role (userid,roleid) VALUES (#{userId},4)")
    int insertUserRole(@Param("userId") String userId);

    @Insert("insert into t_vip (userId,epath) VALUES (#{userId},#{epath})")
    int insertVip(@Param("userId") String userId,@Param("epath") String epath);

    @SelectProvider(type = userType.class, method = "getExpertByAreaCodeAndSubjectIdSql")
    List<Exepert> getEXPERTListByareaCode(@Param("table") Table table, @Param("areaCode") String areaCode,@Param("subjectId") String subjectId);

    @SelectProvider(type = userType.class, method = "getExpertByAreaCodeAndSubjectIdCountSql")
    int getEXPERTCount(@Param("areaCode") String areaCode,@Param("subjectId") String subjectId);

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class userType {
        //专家相关的数据
        public String getExpertByAreaCodeAndSubjectIdSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            String areaCode = (String)map.get("areaCode");
            String subjectId = (String)map.get("subjectId");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT t1.`id`,t1.`name`,t1.`headImage`,t1.`unit`,t1.`introduction`,t1.`telephone`,t1.`address`,t1.`gender`,t1.`score`,t1.`crops`,t4.`longitude`,t4.`latitude`\n" +
                    " FROM t_expertinfo t1 INNER JOIN t_expert_subject t2\n" +
                    " ON t1.`id`=t2.expertId INNER JOIN t_subject t3 ON t2.`subjectId`=t3.`id`\n" +
                    " INNER JOIN t_area t4 ON t4.areaid=t1.`areaid` WHERE 1=1 ");
            sql.append("AND t1.`del_flg`=0 ");
            if(areaCode !=null){
                sql.append(" and t4.`areacode` LIKE '"+areaCode+"%'");
            }
            if(subjectId !=null){
                sql.append(" and  t3.id = "+subjectId);
            }
            sql.append(" ORDER BY t1.`createtime`  desc limit "+table.getStartPoint() + "," + table.getLimit());
            return sql.toString();
        }
        public String getExpertByAreaCodeAndSubjectIdCountSql(Map<String, Object> map){
            //获取总数数据
            String areaCode = (String)map.get("areaCode");
            String subjectId = (String)map.get("subjectId");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT COUNT(*)\n" +
                    " FROM t_expertinfo t1 INNER JOIN t_expert_subject t2\n" +
                    " ON t1.`id`=t2.expertId INNER JOIN t_subject t3 ON t2.`subjectId`=t3.`id`\n" +
                    " INNER JOIN t_area t4 ON t4.areaid=t1.`areaid` WHERE 1=1 ");
            sql.append(" and t1.`del_flg`=0 ");
            if(areaCode !=null){
                sql.append(" and t4.`areacode` LIKE '"+areaCode+"%'");
            }
            if(subjectId !=null){
                sql.append(" and  t3.id = "+subjectId);
            }

            return sql.toString();
        }


    }
}
